﻿SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE cook.AddFavoriteRecipe(@RecipeId bigint, @UserId bigint)
AS
BEGIN
  IF (NOT EXISTS (SELECT 1 FROM cook.FavoriteRecipes fr WHERE fr.RecipeId = @RecipeId AND fr.UserId = @UserId))
  BEGIN
  	INSERT INTO cook.FavoriteRecipes (RecipeId, UserId)
    VALUES(@RecipeId, @UserId);
  END;	
END
GO

CREATE PROCEDURE cook.RemoveFavoriteRecipe(@RecipeId bigint, @UserId bigint)
AS
BEGIN
  DELETE FROM cook.FavoriteRecipes WHERE RecipeId = @RecipeId AND UserId = @UserId;
END
GO

CREATE PROCEDURE cook.AddUserRating(@UserId bigint, @RecipeId bigint, @Rating float)
AS
BEGIN
  IF (EXISTS (SELECT 1 FROM cook.UserRecipeRating urr 
              WHERE urr.UserId = @UserId AND urr.RecipeId = @RecipeId))
  BEGIN
  	UPDATE cook.UserRecipeRating
    SET Rating = @Rating
    WHERE UserId = @UserId AND RecipeId = @RecipeId;
  END
  ELSE
  BEGIN
  	INSERT INTO cook.UserRecipeRating(UserId, RecipeId, Rating)
    values (@UserId, @RecipeId, @Rating);    
  END;
  
  UPDATE cook.Recipes
  SET CurrentRating = (SELECT SUM(Rating)/COUNT(1) FROM cook.UserRecipeRating urr WHERE urr.RecipeId = @RecipeId)
  WHERE ID = @RecipeId;
END
GO

